package studio.raptor.extractor.segment.dao;

import com.google.common.base.Joiner;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;
import studio.raptor.databus.command.EventBasicData;
import studio.raptor.databus.command.EventBusinessData;
import studio.raptor.databus.command.EventMetaData;
import studio.raptor.extractor.common.constant.EventMetaStatus;
import studio.raptor.extractor.common.util.JdbcUtils;
import studio.raptor.extractor.segment.entity.EventMeta;
import studio.raptor.gid.Sequencer;

import javax.sql.DataSource;
import java.awt.*;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by liuyong8 on 2017/7/17.
 */
@Repository
public class EventDao {
  private static final Logger log = LoggerFactory.getLogger(EventDao.class);

  @Autowired
  @Qualifier("extractorDataSource")
  private DataSource dataSource;

  @Autowired
  private Sequencer sequencer;

  private static String INSERT_EVENT_META = "INSERT INTO event_meta("
      + "meta_id,event_kind_id,deliver_time,object_key,event_type,region_id,status,order_type_id,offer_ids,prod_ids,service_offer_ids,offer_spec_attr,service_spec_attr) "
      + "VALUES(?,?, to_date(?,'yyyy-mm-dd hh24:mi:ss'),?,?,?,?,?,?,?,?,?,?)";

  private static String INSERT_EVENT_PARAMS =
      "INSERT INTO event_params(meta_id,param_name,param_value) VALUES(?,?,?)";

  private static String QUERY_EVENT_META =
      "SELECT * FROM event_meta t WHERE t.event_kind_id = ? AND t.status = ? ORDER BY t.meta_id ASC";

  private static String QUERY_EVENT_META_IN_STATUSES =
      "SELECT * FROM event_meta t WHERE t.event_kind_id = ? AND t.status in (%s) ORDER BY t.meta_id ASC";

  private static String UPDATE_EVENTMETA_STATUS =
      "UPDATE event_meta SET status = ? WHERE event_kind_id = ? AND object_key = ?";

  /**
   * 新增一条事件元信息
   * 
   * @param eventMetaData 事件元信息
   */
  public Long addEventMeta(EventBasicData eventBasicData, EventMetaData eventMetaData) {
    Connection connection = null;
    PreparedStatement pstmt = null;
    String sql = INSERT_EVENT_META;
    Long metaId = 0L;

    try {
      connection = dataSource.getConnection();
      pstmt = connection.prepareStatement(sql);
      metaId = sequencer.get("metaId").nextId();
//      meta_id,event_kind_id,deliver_time,object_key,event_type,region_id,status,order_type_id,offer_ids,prod_ids,service_offer_ids,offer_spec_attr,service_spec_attr)
      int i = 0;
      pstmt.setLong(++i, metaId);
      pstmt.setInt(++i, eventBasicData.getKindId());
      pstmt.setString(++i, eventBasicData.getDeliverTime());
      pstmt.setString(++i, eventMetaData.getBusinessNo());
      pstmt.setInt(++i, eventMetaData.getBusinessType().getValue());
      pstmt.setLong(++i, eventMetaData.getRegionId());
      pstmt.setString(++i, EventMetaStatus.STATUS_WAIT.value());
      pstmt.setString(++i, eventMetaData.getOrderTypeIds());
      pstmt.setString(++i, eventMetaData.getOfferIds());
      pstmt.setString(++i, eventMetaData.getProdIds());
      pstmt.setString(++i, eventMetaData.getServiceOfferIds());
      pstmt.setString(++i, eventMetaData.getOfferSpecAttrs());
      pstmt.setString(++i, eventMetaData.getServiceSpecAttrs());
      pstmt.executeUpdate();

      pstmt = connection.prepareStatement(INSERT_EVENT_PARAMS);

      if (eventMetaData.getOfferInstId() != null){
        for (Long offerInstId : eventMetaData.getOfferInstId()) {
          pstmt.setLong(1, metaId);
          pstmt.setString(2, "offer_inst_id");
          pstmt.setString(3, String.valueOf(offerInstId));
          pstmt.addBatch();
        }
      }

      if (eventMetaData.getProdInstId() != null){
        for (Long prodInstId: eventMetaData.getProdInstId()) {
          pstmt.setLong(1, metaId);
          pstmt.setString(2, "prod_inst_id");
          pstmt.setString(3, String.valueOf(prodInstId));
          pstmt.addBatch();
        }
      }

      if (eventMetaData.getAcctId() != null){
        for (Long acctId : eventMetaData.getAcctId()) {
          pstmt.setLong(1, metaId);
          pstmt.setString(2, "acct_id");
          pstmt.setString(3, String.valueOf(acctId));
          pstmt.addBatch();
        }
      }

      if (eventMetaData.getCustId() != null){
        for (Long custId : eventMetaData.getCustId()) {
          pstmt.setLong(1, metaId);
          pstmt.setString(2, "cust_id");
          pstmt.setString(3, String.valueOf(custId));
          pstmt.addBatch();
        }
      }


      pstmt.executeBatch();
      connection.commit();

    } catch (Exception e) {
      log.info("insert event meta failed", e);
      try {
        if (null != connection) {
          connection.rollback();
        }
      } catch (SQLException e1) {
        log.info("roll back fail", e1);
      }
    } finally {
      JdbcUtils.close(pstmt);
      JdbcUtils.close(connection);
    }

    return metaId;
  }

  public List<EventMeta> queryMetasByStatuses(Integer kindId, List<String> statuses) {
    Connection connection = null;
    PreparedStatement pstmt = null;
    List<EventMeta> eventMetas = new ArrayList<>();
    String sql = String.format(QUERY_EVENT_META_IN_STATUSES, Joiner.on(",").join(statuses));

    try {
      connection = dataSource.getConnection();
      pstmt = connection.prepareStatement(sql);
      pstmt.setInt(1, kindId);
      for (int i = 0; i < statuses.size(); i++) {
        pstmt.setString(i + 2, statuses.get(i));;
      }
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        EventMeta eventMeta = new EventMeta();
        // set base information
        eventMeta.setEventKindId(rs.getInt("EVENT_KIND_ID"));
        eventMeta.setObjectKey(rs.getString("OBJECT_KEY"));
        eventMeta.setEventType(rs.getInt("EVENT_TYPE"));
        eventMeta.setRetryTimes(rs.getInt("RETRY_TIMES"));
        eventMeta.setStatus(rs.getString("STATUS"));
        eventMeta.setRemark(rs.getString("REMARK"));
        eventMetas.add(eventMeta);
      }
    } catch (Exception e) {
      log.error("query event meta by kindId and status failed", e);
    } finally {
      JdbcUtils.close(pstmt);
      JdbcUtils.close(connection);
    }
    return eventMetas;

  }

  /**
   * 联合kindId和状态查询eventMetaList
   * 
   * @param kindId 事件来源id
   * @param eventMetaStatus eventMeta status
   * @return
   */
  public List<EventMeta> queryEventMetaByKindIdAndStatus(Integer kindId, String eventMetaStatus) throws Exception {
    Connection connection = null;
    PreparedStatement pstmt = null;
    List<EventMeta> eventMetas = new ArrayList<>();
    // String sql = String.format(FIND_FAILED_EVENT, Joiner.on(",").join(objectKeys));
    String sql = QUERY_EVENT_META;
    try {
      connection = dataSource.getConnection();
      pstmt = connection.prepareStatement(sql);
      pstmt.setInt(1, kindId);
      pstmt.setString(2, eventMetaStatus);
      ResultSet rs = pstmt.executeQuery();
      while (rs.next()) {
        EventMeta eventMeta = new EventMeta();
        // set base information
        eventMeta.setEventKindId(rs.getInt("EVENT_KIND_ID"));
        eventMeta.setObjectKey(rs.getString("OBJECT_KEY"));
        eventMeta.setEventType(rs.getInt("EVENT_TYPE"));
        eventMeta.setRetryTimes(rs.getInt("RETRY_TIMES"));
        eventMeta.setStatus(rs.getString("STATUS"));
        eventMeta.setRemark(rs.getString("REMARK"));
        eventMetas.add(eventMeta);
      }
    } catch (Exception e) {
      log.error("query event meta by kindId and status failed", e);
      throw e;
    } finally {
      JdbcUtils.close(pstmt);
      JdbcUtils.close(connection);
    }
    return eventMetas;
  }


  /**
   * 更新历史记录
   * 
   * @param kindId 事件来源id
   * @param objectKey 唯一主键
   */
  /**
   * 更新事件状态
   * 
   * @param eventMeta 事件元信息
   */
  public void updateEventMetaStatusByKindIdAndObjectKey(EventMeta eventMeta) throws SQLException {
    Connection connection = null;
    PreparedStatement pstmt = null;
    List<EventMeta> eventMetas = new ArrayList<>();
    // String sql = String.format(FIND_FAILED_EVENT, Joiner.on(",").join(objectKeys));
    String sql = UPDATE_EVENTMETA_STATUS;
    try {
      connection = dataSource.getConnection();
      pstmt = connection.prepareStatement(sql);
      pstmt.setString(1, eventMeta.getStatus());
      pstmt.setInt(2, eventMeta.getEventKindId());
      pstmt.setString(3, eventMeta.getObjectKey());
      pstmt.executeUpdate();
    } catch (SQLException e) {
      log.error("query event meta by kindId and status failed", e);
      throw e;
    } finally {
      JdbcUtils.close(pstmt);
      JdbcUtils.close(connection);
    }
  }
}
